﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace QLBenhVien
{
    class DAODanhSachKham
    {
        public static string table_name = "DanhSachKham";
        public static void Add(DBODanhSachKham o)
        {
            string sql = "select * from " + table_name;
            DataTable dt = DB_Provider.ReadSchema(sql);
            DataRow dr = dt.NewRow();
            foreach (PropertyDescriptor pro in TypeDescriptor.GetProperties(o))
            {
                dr[pro.Name] = pro.GetValue(o);
            }
            dt.Rows.Add(dr);
            DB_Provider.Write(dt, table_name);
        }

        public static void CapNhatSoThuTu(DBODanhSachKham o)
        {
            string sql = string.Format("update {0} set sothutu = (select max(sothutu)+1 from {0} where ngaykham = @ngaykham) where id=@id and id != 0",table_name);
            try
            {
                SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add("@ngaykham", SqlDbType.Date).Value = DateTime.Now.ToShortDateString();
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = o.id;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                
            }
        }

        public static void DaThamKham(int id)
        {
            string sql = string.Format("update {0} set dakham = 1 where id=@id", table_name);
            try
            {
                SqlConnection conn = new SqlConnection(DB_Provider.conn_str);
                SqlCommand cmd = new SqlCommand(sql, conn);                
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {

            }
        }

        public static DataTable LoadAll()
        {
            string sql = "select * from " + table_name;
            return DB_Provider.Read(sql);
        }

        public static DataTable LoadAll(DateTime dtime)
        {
            string sql = string.Format("select {0}.id,{0}.ngaykham,{0}.sothutu,users.fullname TenNhanVien,HoSoBenhNhan.hoten TenBenhNhan,HoSoBenhNhan.ngaysinh,{0}.benhnhan_id,{0}.nhanvien_id,{0}.dakham from {0} join HoSoBenhNhan on HoSoBenhNhan.id = {0}.benhnhan_id join users on users.id = {0}.nhanvien_id where ngaykham = @ngaykham order by {0}.id desc", table_name);
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, DB_Provider.conn_str);
                da.SelectCommand.Parameters.Add("@ngaykham", SqlDbType.Date).Value = dtime.ToShortDateString();
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                return null;
            }
            return dt;
        }
    }
}
